﻿USE [FD]
GO
/****** Object:  StoredProcedure [dbo].[fd_Folio_gets_by_document]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Folio_gets_by_document]
GO
/****** Object:  Table [dbo].[Indices]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Indices]
GO
/****** Object:  StoredProcedure [dbo].[fd_Linea_gets_by_folio]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Linea_gets_by_folio]
GO
/****** Object:  StoredProcedure [dbo].[fd_getFolioNoWherePageIsIn]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_getFolioNoWherePageIsIn]
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuario_get_by_id_and_password]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Usuario_get_by_id_and_password]
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuario_get_by_username_and_password]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Usuario_get_by_username_and_password]
GO
/****** Object:  StoredProcedure [dbo].[fd_Expediente_Gets]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Expediente_Gets]
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuarios_All]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Usuarios_All]
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuario_Create]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Usuario_Create]
GO
/****** Object:  StoredProcedure [dbo].[fd_Portada_get_by_Expediente]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Portada_get_by_Expediente]
GO
/****** Object:  StoredProcedure [dbo].[fd_Portada_Create]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Portada_Create]
GO
/****** Object:  StoredProcedure [dbo].[fd_Expediente_Create]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Expediente_Create]
GO
/****** Object:  StoredProcedure [dbo].[fd_Folio_get_by_id]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Folio_get_by_id]
GO
/****** Object:  StoredProcedure [dbo].[fd_Document_get_by_code]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Document_get_by_code]
GO
/****** Object:  Table [dbo].[Expedientes]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Expedientes]
GO
/****** Object:  StoredProcedure [dbo].[fd_Document_get_by_Expediente]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Document_get_by_Expediente]
GO
/****** Object:  StoredProcedure [dbo].[fd_Documento_Gets]    Script Date: 11/24/2011 13:37:08 ******/
DROP PROCEDURE [dbo].[fd_Documento_Gets]
GO
/****** Object:  Table [dbo].[Documentos]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Documentos]
GO
/****** Object:  Table [dbo].[TipoDocumentos]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[TipoDocumentos]
GO
/****** Object:  Table [dbo].[Folios]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Folios]
GO
/****** Object:  Table [dbo].[Lineas]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Lineas]
GO
/****** Object:  Table [dbo].[Usuarios]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Usuarios]
GO
/****** Object:  Table [dbo].[Portadas]    Script Date: 11/24/2011 13:37:08 ******/
DROP TABLE [dbo].[Portadas]
GO
/****** Object:  User [sa1]    Script Date: 11/24/2011 13:37:08 ******/
DROP USER [sa1]
GO
/****** Object:  User [sa1]    Script Date: 11/24/2011 13:37:08 ******/
CREATE USER [sa1] FOR LOGIN [sa1] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  Table [dbo].[Portadas]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Portadas](
	[id] [uniqueidentifier] NOT NULL,
	[NoExpediente] [varchar](50) NOT NULL,
	[Num_Folios] [int] NOT NULL CONSTRAINT [DF_Table_1_Folios]  DEFAULT ((0)),
	[Asunto] [varchar](100) NULL,
	[Unidad_Remitente] [varchar](100) NULL,
	[Funcionario_Remitente] [nvarchar](100) NULL,
	[CodPortada] [nvarchar](50) NOT NULL CONSTRAINT [DF_Portadas_NoPortada]  DEFAULT ((0)),
	[CodDocumento] [varchar](50) NULL,
 CONSTRAINT [PK_Portadas] PRIMARY KEY CLUSTERED 
(
	[CodPortada] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador único de la tabla Portadas' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre del Expediente al cual pertenece' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'NoExpediente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cantidad de Folios que contiene el documento o el expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'Num_Folios'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descripción general del Expediente o del documento al cual pertenece' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'Asunto'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unidad administrativa al cual pertenece el expdiente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'Unidad_Remitente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Funcionario que ingresa el Documento o el expedietne al sistema' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'Funcionario_Remitente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código identificador de la portada en el Documento o expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'CodPortada'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Codigo identificador del Documento en el expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Portadas', @level2type=N'COLUMN',@level2name=N'CodDocumento'
GO
/****** Object:  Table [dbo].[Usuarios]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Usuarios](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[Nombre1] [varchar](50) NOT NULL,
	[Nombre2] [varchar](50) NULL,
	[Apellido1] [varchar](50) NOT NULL,
	[Apellido2] [varchar](50) NULL,
	[Departamento] [varchar](50) NOT NULL,
	[Administrador] [bit] NOT NULL CONSTRAINT [DF_Usuarios_Administrador]  DEFAULT ((0)),
 CONSTRAINT [PK_Usuarios_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador único de la tabla' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'usuario del sistema' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Username'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Clave de acceso al Sistema' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Password'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primer Nombre del Usuario' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Nombre1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Segundo Nombre del Usuario' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Nombre2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primer Apellido del Usuario' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Apellido1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Segundo Apellido del Usuario' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Apellido2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre del Departamento al que pertenece el Usuario' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Usuarios', @level2type=N'COLUMN',@level2name=N'Departamento'
GO
/****** Object:  Table [dbo].[Indices]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Indices](
	[id] [uniqueidentifier] NULL,
	[CodIndice] [varchar](50) NOT NULL,
	[NumLinea] [int] NOT NULL CONSTRAINT [DF_Indices_NumLinea]  DEFAULT ((0)),
	[Referencia] [varchar](50) NULL,
	[NoExpediente] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Indices] PRIMARY KEY CLUSTERED 
(
	[CodIndice] ASC,
	[NoExpediente] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador único de la tabla de indices' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Indices', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código del Indice' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Indices', @level2type=N'COLUMN',@level2name=N'CodIndice'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Número de la línea referenciada' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Indices', @level2type=N'COLUMN',@level2name=N'NumLinea'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Texto de referencia' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Indices', @level2type=N'COLUMN',@level2name=N'Referencia'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre del Expediente al cual pertenece el Indice' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Indices', @level2type=N'COLUMN',@level2name=N'NoExpediente'
GO
/****** Object:  StoredProcedure [dbo].[fd_Expediente_Gets]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Expediente_Gets]
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		IF exists (select * from Expedientes )							
   			BEGIN
   				select	* from Expedientes 							
   			END
   		ELSE
   			BEGIN
   				RAISERROR ('No hay ningun Expediente Disponible.', 0, 1);
   			END   		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Expediente_Create]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Expediente_Create]
(
	@expediente	TEXT,
	@ID INT OUTPUT
)
AS


BEGIN

	BEGIN TRY
	
		DECLARE @XMLData XML
		
		SET @XMLData = CONVERT(XML, @expediente)
		
		INSERT INTO Expedientes 
			SELECT a.b.value('NoExpediente[1]','varchar(50)') AS NoExpediente,
					getdate() AS StartDate,
					getdate() AS ModifyDate,
				   --a.b.value('StartDate[1]','datetime') AS StartDate,
				   --a.b.value('ModifyDate[1]','datetime') AS ModifyDate,
				   a.b.value('CreatedBy[1]','varchar(50)') AS CreatedBy,
				   a.b.value('ModifiedBy[1]','varchar(50)') AS ModifiedBy,
				   a.b.value('Descripcion[1]','varchar(250)') AS Descripcion
			FROM @XMLData.nodes('Expediente') a(b);
			
		SELECT ID = SCOPE_IDENTITY()
		
	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
					@ErrorSeverity	INT,
					@ErrorState		INT,
					@ErrorProcedure NVARCHAR(126),
					@ErrorLine		INT,
					@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
					@ErrorSeverity = ERROR_SEVERITY(),
					@ErrorState = ERROR_STATE(),
					@ErrorProcedure = ERROR_PROCEDURE(),
					@ErrorLine = ERROR_LINE(),
					@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH

END
GO
/****** Object:  Table [dbo].[Folios]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Folios](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[NumFolio] [int] NOT NULL,
	[CodDocumento] [varchar](50) NOT NULL,
	[NumLineas] [int] NULL,
	[NoExpediente] [varchar](50) NOT NULL,
	[Tipo] [int] NOT NULL CONSTRAINT [DF_Folios_Tipo]  DEFAULT ((0)),
 CONSTRAINT [PK_Folios_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador Unico de la tabla Folios' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Folios', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Número de folio del ddocumento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Folios', @level2type=N'COLUMN',@level2name=N'NumFolio'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código Alfanumerico que identifica el documento al que pertenece' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Folios', @level2type=N'COLUMN',@level2name=N'CodDocumento'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cantidad de líneas que contiene el Folio' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Folios', @level2type=N'COLUMN',@level2name=N'NumLineas'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código Alfanumérico que Identifica el expediente al que pertenece' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Folios', @level2type=N'COLUMN',@level2name=N'NoExpediente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tipo de archivo. ( Texto o multimedia)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Folios', @level2type=N'COLUMN',@level2name=N'Tipo'
GO
/****** Object:  Table [dbo].[Lineas]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Lineas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[NumLinea] [int] NOT NULL,
	[NumFolio] [int] NOT NULL,
	[NoExpediente] [varchar](50) NOT NULL,
	[CodDocumento] [varchar](50) NOT NULL,
	[Texto] [text] NULL,
 CONSTRAINT [PK_Lineas] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificardor unico de la tabla' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Lineas', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Número de línea del Folio' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Lineas', @level2type=N'COLUMN',@level2name=N'NumLinea'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Número de Folio del Documento alq ue petenece la línea' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Lineas', @level2type=N'COLUMN',@level2name=N'NumFolio'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código del Expediente al que pertenece el documento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Lineas', @level2type=N'COLUMN',@level2name=N'NoExpediente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código del Documento al que pertenece el Folio' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Lineas', @level2type=N'COLUMN',@level2name=N'CodDocumento'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Texto contenido en la línea' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Lineas', @level2type=N'COLUMN',@level2name=N'Texto'
GO
/****** Object:  StoredProcedure [dbo].[fd_Document_get_by_code]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Document_get_by_code]
(
	@codeDoc varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		
   		select	* from Documentos where CodDocumento = @codeDoc		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  Table [dbo].[Expedientes]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Expedientes](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[NoExpediente] [varchar](50) NOT NULL,
	[StartDate] [datetime] NOT NULL CONSTRAINT [DF_Expedientes_StartDate]  DEFAULT (getdate()),
	[ModifyDate] [datetime] NOT NULL,
	[CreatedBy] [varchar](50) NULL,
	[ModifyBy] [varchar](50) NULL,
	[Descripcion] [varchar](250) NULL,
 CONSTRAINT [PK_Expedientes_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Campo Identificador Autonumérico de la tabla Expedientes.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre del Expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes', @level2type=N'COLUMN',@level2name=N'NoExpediente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha de Creacion del Expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes', @level2type=N'COLUMN',@level2name=N'StartDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha de la Ultima Modificacion del expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes', @level2type=N'COLUMN',@level2name=N'ModifyDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Usuario que creo el expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes', @level2type=N'COLUMN',@level2name=N'CreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Usuario que realizó la última modificación del expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes', @level2type=N'COLUMN',@level2name=N'ModifyBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tabla de almacenamiento de expedientes parafoliar' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Expedientes'
GO
/****** Object:  StoredProcedure [dbo].[fd_Document_get_by_Expediente]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Document_get_by_Expediente]
(
	@noExpediente varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		
   		select	* from Documentos where NoExpediente = @noExpediente		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Documento_Gets]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Documento_Gets]
(
	@NoExpediente varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		IF exists (select	* from Documentos where NoExpediente = @NoExpediente						)							
   			BEGIN
   				select	* from Documentos where NoExpediente = @NoExpediente						
   			END
   		ELSE
   			BEGIN
   				RAISERROR ('No hay ningun Documento Disponible.', 0, 1);
   			END   		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  Table [dbo].[Documentos]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Documentos](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[CodDocumento] [varchar](50) NOT NULL,
	[NoExpediente] [varchar](50) NOT NULL,
	[NumPaginas] [int] NULL,
	[Descripción] [varchar](100) NULL,
	[Fecha_Documento] [datetime] NULL,
	[Fecha_Recepcion] [datetime] NOT NULL CONSTRAINT [DF_Documentos_Fecha_Recepcion]  DEFAULT (getdate()),
	[Tipo] [varchar](50) NOT NULL CONSTRAINT [DF_Documentos_Tipo]  DEFAULT ((0)),
	[Usuario] [varchar](50) NULL,
	[documento] [varchar](50) NULL,
 CONSTRAINT [PK_Documentos] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Campo Identificador Autonumérico de la tabla Documentos.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Código Alfanumerico del Documento en el Expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'CodDocumento'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nombre o Codigo del expediente al cual pertenece el Documento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'NoExpediente'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cantidad de Páginas que contiene el Documento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'NumPaginas'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descripción general del contenido del documento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'Descripción'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha de Creación del Documento' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'Fecha_Documento'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha en que el documento es ingresado en el expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'Fecha_Recepcion'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tipo de documento. (1 Texto, 2 Multimedia)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'Tipo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador del usuario que incluyó los documentos en el expediente' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Documentos', @level2type=N'COLUMN',@level2name=N'Usuario'
GO
/****** Object:  Table [dbo].[TipoDocumentos]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TipoDocumentos](
	[Tipo] [int] NOT NULL,
	[Descripcion] [varchar](100) NULL,
 CONSTRAINT [PK_TipoDocumentos] PRIMARY KEY CLUSTERED 
(
	[Tipo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tipo de datos que contiene el Documento o Folio' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TipoDocumentos', @level2type=N'COLUMN',@level2name=N'Tipo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Descripción del tipo de datos que contiene el Documento o folio' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TipoDocumentos', @level2type=N'COLUMN',@level2name=N'Descripcion'
GO
/****** Object:  StoredProcedure [dbo].[fd_Portada_get_by_Expediente]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Portada_get_by_Expediente]
(
	@NoExpediente	varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
		--Declare local variable
				
   	BEGIN TRY
   		IF exists (select NoExpediente from Portadas where NoExpediente = @NoExpediente)
   			BEGIN
   				select * from Portadas where NoExpediente = @NoExpediente;
   			END
   		ELSE
   			BEGIN
   				RAISERROR ('El Expediente no contiene Portada', 0, 1);
   			END   		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Portada_Create]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Portada_Create]
(
	@portada	TEXT,
	@ID INT OUTPUT
)
AS


BEGIN

	BEGIN TRY
	
		DECLARE @XMLData XML
		
		SET @XMLData = CONVERT(XML, @portada)
		
		INSERT INTO Portadas 
			SELECT a.b.value('NoExpediente[1]','varchar(50)') AS NoExpediente,
				   a.b.value('NumFolios[1]','int') AS NumFolios,
				   a.b.value('Asunto[1]','varchar(100)') AS Asunto,
				   a.b.value('UnidadRemitente[1]','varchar(100)') AS UnidadRemitente,
				   a.b.value('FuncionarioRemitente[1]','varchar(100)') AS FuncionarioRemitente,
				   a.b.value('CodPortada[1]','varchar(50)') AS CodPortada,
				   a.b.value('CodDocumento[1]','varchar(50)') AS CodDocumento,
				   a.b.value('Fecha[1]','datetime') AS Fecha	

			FROM @XMLData.nodes('Portada') a(b);
			
		SELECT ID = SCOPE_IDENTITY()
		
	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
					@ErrorSeverity	INT,
					@ErrorState		INT,
					@ErrorProcedure NVARCHAR(126),
					@ErrorLine		INT,
					@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
					@ErrorSeverity = ERROR_SEVERITY(),
					@ErrorState = ERROR_STATE(),
					@ErrorProcedure = ERROR_PROCEDURE(),
					@ErrorLine = ERROR_LINE(),
					@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH

END
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuarios_All]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Usuarios_All]
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		IF exists (select * from Usuarios )							
   			BEGIN
   				select	* from Usuarios 							
   			END
   		ELSE
   			BEGIN
   				RAISERROR ('No hay ningun Usuario Disponible.', 0, 1);
   			END   		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuario_Create]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Usuario_Create]
(
	@usuario	TEXT,
	@ID INT OUTPUT
)
AS

BEGIN

	BEGIN TRY
	
		DECLARE @XMLData XML
		
		SET @XMLData = CONVERT(XML, @usuario)
		
		INSERT INTO Usuarios 
			SELECT a.b.value('Username[1]','varchar(50)') AS Username,
				   a.b.value('Password[1]','varchar(50)') AS Password,
				   a.b.value('Nombre1[1]','varchar(50)') AS Nombre1,
				   a.b.value('Nombre2[1]','varchar(50)') AS Nombre2,
				   a.b.value('Apellido1[1]','varchar(50)') AS Apellido1,
				   a.b.value('Apellido2[1]','varchar(50)') AS Apellido2,
				   a.b.value('Departamento[1]','varchar(50)') AS Departamento,
				   a.b.value('Administrador[1]','bit') AS Departamento
				   
			FROM @XMLData.nodes('Usuario') a(b);
			
		SELECT ID = SCOPE_IDENTITY()
		
	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
					@ErrorSeverity	INT,
					@ErrorState		INT,
					@ErrorProcedure NVARCHAR(126),
					@ErrorLine		INT,
					@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
					@ErrorSeverity = ERROR_SEVERITY(),
					@ErrorState = ERROR_STATE(),
					@ErrorProcedure = ERROR_PROCEDURE(),
					@ErrorLine = ERROR_LINE(),
					@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH

END
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuario_get_by_id_and_password]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Usuario_get_by_id_and_password]
(
	@username	varchar(50),
	@password varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
		--Declare local variable
				
   	BEGIN TRY
   		IF exists (select Usuario from Usuarios where 
			Usuario = @username AND clave = @password)
   			BEGIN
   				SELECT * FROM Usuarios WHERE Usuario = @username AND clave = @password;
   			END
   		ELSE
   			BEGIN
   				RAISERROR ('Nombre de usuario o contraseña invalida.', 1, 1);
   			END   		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Usuario_get_by_username_and_password]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Usuario_get_by_username_and_password]
(
	@username	varchar(50),
	@password varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
		--Declare local variable
				
   	BEGIN TRY
   		IF exists (select Username from Usuarios where 
   			Username = @username AND Password = @password)
   			BEGIN
   				select * from Usuarios where Username = @username AND Password = @password;
   			END
   		ELSE
   			BEGIN
   				RAISERROR ('Nombre de usuario o contraseña invalida.', 0, 1);
   			END   		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Folio_get_by_id]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Folio_get_by_id]
(
	@id int
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		
   		select	* from Folios where id = @id		
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Folio_gets_by_document]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Folio_gets_by_document]
(
	@codeDoc varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		
   		select	* from Folios where CodDocumento = @codeDoc order by ABS(NumFolio)
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_Linea_gets_by_folio]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_Linea_gets_by_folio]
(
	@NumFolio int,
	@NumExpediente varchar(50),
	@CodDocumento varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		
   		select	* from Lineas where NumFolio = @NumFolio AND NoExpediente = @NumExpediente AND @CodDocumento = CodDocumento
   		order by ABS(NumLinea);	
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[fd_getFolioNoWherePageIsIn]    Script Date: 11/24/2011 13:37:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fd_getFolioNoWherePageIsIn]
(
	@noPage int,
	@codeDoc varchar(50)
)
AS

BEGIN

	SET NOCOUNT ON
	
				
   	BEGIN TRY
   		
   		select	NumFolio from Lineas where CodDocumento = @codeDoc and NumLinea = @nopage
   		
   	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
				@ErrorSeverity	INT,
				@ErrorState		INT,
				@ErrorProcedure NVARCHAR(126),
				@ErrorLine		INT,
				@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorProcedure = ERROR_PROCEDURE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH
END
GO
